package h5utils

import (
    _ "github.com/go-sql-driver/mysql" 
    "database/sql"
    "encoding/json"
    "fmt"
    "strconv"  
)

var db *sql.DB
 
func Init() {
    db, _ = sql.Open("mysql", "root:qwer2013@tcp(127.0.0.1:3306)/EProtect?charset=utf8")
    db.SetMaxOpenConns(2000)
    db.SetMaxIdleConns(1000)
    db.Ping()
}

func IfStudentExsist(sid string) bool{
    sql := "SELECT * FROM StudentInfo where Sid = '" + sid + "'"
    rows, err := db.Query(sql)
    defer rows.Close()
    CheckErr(err)
    if rows.Next() {
        return true
    } else {
        return false
    }
}

func IfSchoolExsist(schoolName string, className string) bool{
    sql := "SELECT * FROM TreeInfo where SchoolName = '" + schoolName + "' and class = '" + className + "'"
    rows, err := db.Query(sql)
    defer rows.Close()
    CheckErr(err)
    if rows.Next() {
        return true
    } else {
        return false
    }
}

func SelectStudentInfo(sid string)  string{
    sql := "select a.*, b.Count,b.CouldCount, b.IfShare,  b.sid"
    sql = sql + " from TreeInfo a, StudentInfo b where a.Tid = b.Tid and b.sid = '" + sid + "'"
    mStudentInfoMap := SelectData(sql)
    resultJson, _ := json.Marshal(mStudentInfoMap)
    fmt.Println(resultJson)
    return string(resultJson)
}

func SelectData(sql string) map[string]string{
    fmt.Println(sql)
    rows, err := db.Query(sql)
    defer rows.Close()
    CheckErr(err)

    columns, _ := rows.Columns()
    scanArgs := make([]interface{}, len(columns))
    values := make([]interface{}, len(columns))
    for i := range values {
        scanArgs[i] = &values[i]
    }

    record := make(map[string]string)    
    if rows.Next() {
        //将行数据保存到record字典
        err = rows.Scan(scanArgs...)    
        for i, col := range values {
            if col != nil {
                record[columns[i]] = string(col.([]byte))
            }
        }
    }

    fmt.Println("record map: ", record)
    return record
}


func Select10Top(sid string) string {
    rows, err := db.Query("select * from TreeInfo order by WaterAmount desc limit 10")
    CheckErr(err)

    top10Json := ""
    defer rows.Close()
    var tid int
    var schoolName  string
    var className   string
    var waterNumber string
    countNo := 1
    for rows.Next() { 
        if (countNo > 1) {
            top10Json += " , "
        }
        row_err := rows.Scan(&tid, &schoolName, &className, &waterNumber)
        if row_err != nil {
            return "{\"data\":\"sql error!\"}"
        } else {
            top10Json = top10Json + "\"" + strconv.Itoa(countNo) + "\":{\"tid\":\"" + strconv.Itoa(tid) + "\", \"schoolName\":\"" 
            top10Json = top10Json + schoolName + "\", \"className\":\"" + className + "\", \"waterNumber\":\"" + waterNumber + "\"}" 
        }
        countNo ++
    }
    sql := "select a.*, b.Count, b.CouldCount, b.IfShare, b.sid"
    sql = sql + " from TreeInfo a, StudentInfo b where a.Tid = b.Tid and b.sid = '" + sid + "'"
    mStudentInfoMap := SelectData(sql)
    top10Json = top10Json + ", \"11\":{\"tid\":\"" + mStudentInfoMap["Tid"] + "\", \"schoolName\":\"" + mStudentInfoMap["SchoolName"]
    top10Json = top10Json + "\", \"className\":\"" + mStudentInfoMap["Class"] + "\", \"waterAmount\":\"" + mStudentInfoMap["WaterAmount"] + "\"}" 
    return "{" + top10Json + "}" 
}

func InsertTree(schoolName string, className string) {
    stmt, err := db.Prepare("INSERT INTO TreeInfo (SchoolName, Class) VALUES(?, ?)")
    // defer stmt.close()
    stmt.Exec(schoolName, className)
    CheckErr(err)
}

func InsertStudent(sid string, tid string) {
    stmt, err := db.Prepare("INSERT INTO StudentInfo (SchoolName, Class) VALUES(?, ?)")
    // defer stmt.close()
    stmt.Exec(sid, tid)
    CheckErr(err)
}

func CheckErr(err error){ 
    if err != nil {
        panic(err) 
    }
}